package cn.edu.my.test;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

public class TestData{
    
	public static void main(String[] args){
		//testDBUtilInsert();
		//testDBUtilDelete();
		//testDBUtiUpdate();
		//testDBUtilSelectOneByMap();
		//testDBUtilSelectManyByMap();
		//testDBUtilSelectOneByBean();
		//testDBUtilSelectOneByArray();
		//testDBUtilSelectManyByArray();
		//testDBUtilSelectByColumnListHandler();
		testDBUtilSelectByScalarHandler();
	}
	
	public static void testDBUtilSelectByScalarHandler() {
		String url = "jdbc:oracle:thin:@localhost:1521:mytool";
		String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
		String user = "scott";
		String password = "tiger";
		 
		Connection conn = null;
		  
		QueryRunner queryRunner = null;
		
		try {
			DbUtils.loadDriver(jdbcDriver);
		    conn = DriverManager.getConnection(url, user, password);
			queryRunner = new QueryRunner();
			
			System.out
                    .println("使用ScalarHandler处理单行记录，只返回结果集第一行中的指定字段，如未指定字段，则返回第一个字段！");
            Object scalar1 = queryRunner.query(conn, "select * from stock_info",
                    new ScalarHandler("name"));
            System.out.println(scalar1);
            Object scalar2 = queryRunner.query(conn,
                    "select * from stock_info", new ScalarHandler());
            System.out.println(scalar2);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        DbUtils.closeQuietly(conn);
        System.out.println("insert is successful!");
	}
	
	public static void testDBUtilSelectByColumnListHandler() {
		String url = "jdbc:oracle:thin:@localhost:1521:mytool";
		String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
		String user = "scott";
		String password = "tiger";
		 
		Connection conn = null;
		  
		QueryRunner queryRunner = null;
		
		try {
			DbUtils.loadDriver(jdbcDriver);
		    conn = DriverManager.getConnection(url, user, password);
			queryRunner = new QueryRunner();
			
			System.out.println("使用ColumnListHandler处理单行记录，返回其中指定的一列！");
            List<Object> colList = (List<Object>)queryRunner.query(conn,
                    "select * from stock_info where rownum=1", new ColumnListHandler(
                            "num"));
            for (Iterator<Object> itr = colList.iterator(); itr.hasNext();) {
                System.out.println(itr.next());
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        DbUtils.closeQuietly(conn);
        System.out.println("insert is successful!");
	}
	
	public static void testDBUtilSelectManyByArray() {
		String url = "jdbc:oracle:thin:@localhost:1521:mytool";
		String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
		String user = "scott";
		String password = "tiger";
		 
		Connection conn = null;
		  
		QueryRunner queryRunner = null;
		
		try {
			DbUtils.loadDriver(jdbcDriver);
		    conn = DriverManager.getConnection(url, user, password);
			queryRunner = new QueryRunner();

			System.out.println("使用Array处理多行记录！");
            List<Object[]> arraylist = queryRunner
                    .query(conn, "select * from stock_info where rownum<=3",
                            new ArrayListHandler());
  
            for (Iterator<Object[]> itr = arraylist.iterator(); itr.hasNext();) {
                Object[] a = itr.next();
                System.out.println("--------------");
                for (int i = 0; i < a.length; i++) {
                    System.out.println(a[i]);
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        DbUtils.closeQuietly(conn);
        System.out.println("insert is successful!");
	}
	
	public static void testDBUtilSelectOneByArray() {
		String url = "jdbc:oracle:thin:@localhost:1521:mytool";
		String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
		String user = "scott";
		String password = "tiger";
		 
		Connection conn = null;
		  
		QueryRunner queryRunner = null;
		
		try {
			DbUtils.loadDriver(jdbcDriver);
		    conn = DriverManager.getConnection(url, user, password);
			queryRunner = new QueryRunner();
			
			System.out.println("使用Array处理单行记录！");  
            Object[] array = queryRunner.query(conn,  
                    "select * from stock_info where rownum=1", new ArrayHandler());  
  
            for (int i = 0; i < array.length; i++) {  
                System.out.println(array[i]);  
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        DbUtils.closeQuietly(conn);
        System.out.println("insert is successful!");
	}
	
	public static void testDBUtilSelectOneByBean() {
		String url = "jdbc:oracle:thin:@localhost:1521:mytool";
		String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
		String user = "scott";
		String password = "tiger";
		 
		Connection conn = null;
		  
		QueryRunner queryRunner = null;
		
		try {
			DbUtils.loadDriver(jdbcDriver);
		    conn = DriverManager.getConnection(url, user, password);
			queryRunner = new QueryRunner();
			
			System.out.println("使用Bean处理单行记录！");
              
            StockInfo si = queryRunner.query(conn,
                    "select * from stock_info where rownum=1",
                    new BeanHandler<StockInfo>(StockInfo.class));
            System.out.println("num=" + si.getNum());
            System.out.println("name=" + si.getName());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        DbUtils.closeQuietly(conn);
        System.out.println("insert is successful!");
	}
	
	public static void testDBUtilSelectOneByMap() {
		String url = "jdbc:oracle:thin:@localhost:1521:mytool";
		String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
		String user = "scott";
		String password = "tiger";
		 
		Connection conn = null;
		  
		QueryRunner queryRunner = null;
		
		try {
			DbUtils.loadDriver(jdbcDriver);
		    conn = DriverManager.getConnection(url, user, password);
			queryRunner = new QueryRunner();
			
			// 返回单行记录，使用Map
            System.out.println("使用Map处理单行记录！");
            Map<String, Object> map = queryRunner.query(conn,
                    "select * from stock_info where rownum=1", new MapHandler(),
                    (Object[]) null);
  
            for (Iterator<Entry<String, Object>> i = map.entrySet().iterator(); i.hasNext();) {
                Entry<String, Object> e = i.next();
                System.out.println(e.getKey() + "=" + e.getValue());
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        DbUtils.closeQuietly(conn);
        System.out.println("insert is successful!");
	}
	
	public static void testDBUtiUpdate() {
		String url = "jdbc:oracle:thin:@localhost:1521:mytool";
		String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
		String user = "scott";
		String password = "tiger";
		 
		Connection conn = null;
		  
		QueryRunner queryRunner = null;
		
		try {
			DbUtils.loadDriver(jdbcDriver);
		    conn = DriverManager.getConnection(url, user, password);
			queryRunner = new QueryRunner();
			
			// 修改示例  
            queryRunner.update(conn,  
                    "update stock_info set name = ? where num=?", "new", 6);  
  
            System.out.println("最终结果显示结果");  
            List<Map<String, Object>> list = queryRunner.query(conn,  
                    "select * from stock_info", new MapListHandler(),  
                    (Object[]) null);  
  
            for (Iterator<Map<String, Object>> li = list.iterator(); li.hasNext();) {  
                System.out.println("--------------");  
                Map<String, Object> m = li.next();  
                for (Iterator<Entry<String, Object>> mi = m.entrySet().iterator(); mi.hasNext();) {  
                    Entry<String, Object> e = mi.next();  
                    System.out.print(e.getValue());  
                    System.out.print(",");  
                }  
                System.out.println();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        DbUtils.closeQuietly(conn);
        System.out.println("insert is successful!");
	}
	
	public static void testDBUtilDelete() {
		String url = "jdbc:oracle:thin:@localhost:1521:mytool";
		String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
		String user = "scott";
		String password = "tiger";
		 
		Connection conn = null;
		  
		QueryRunner queryRunner = null;
		
		try {
			DbUtils.loadDriver(jdbcDriver);
		    conn = DriverManager.getConnection(url, user, password);
		  
		    queryRunner = new QueryRunner();
			
            // 删除示例
            queryRunner.update(conn, "delete from stock_info where num=1");
            queryRunner.update(conn, "delete from stock_info where num=?", 2);
            queryRunner.batch(conn, "delete from stock_info where num=?",
                    new Object[][] { { 3 }, { 4 } });
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        DbUtils.closeQuietly(conn);
        System.out.println("insert is successful!");
	}
	
	public static void testDBUtilInsert() {
		String url = "jdbc:oracle:thin:@localhost:1521:mytool";
		String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
		String user = "scott";
		String password = "tiger";
		 
		Connection conn = null;
		  
		QueryRunner queryRunner = null;
		
		try {
			DbUtils.loadDriver(jdbcDriver);
		    conn = DriverManager.getConnection(url, user, password);
		  
		    queryRunner = new QueryRunner();
			
            queryRunner.update(conn, "delete from stock_info");
            // queryRunner.update(conn, "truncate table stock_info");
            // 插一条
            for (int i = 0; i < 10; i++) {
                queryRunner.update(conn,
                        "insert into stock_info (num,name) values (?,?)", i,
                        "123");
            }
            
            // 再插多条  
            queryRunner.batch(conn,  
                    "insert into stock_info (num,name) values (?,?)",  
                    new Object[][] { { 11, "abc" },  
                            { 12, "opr" } });
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        DbUtils.closeQuietly(conn);
        System.out.println("insert is successful!");
	}
}